*===============================================================================
* Project title		Savings Accounts to Borrow Less	
* PIs			Felipe Kast, Dina Pomeranz 
* File name		3f_tables_ancova_savings.do
*-------------------------------------------------------------------------------
* Description		Creates savings ANCOVA table  
* Outputs		Table A14 
* Spaces per tab 	8 
*===============================================================================

*===============================================================================
*TOC
*1)	Prepare data for analysis
*2)	Tables
*2.1)	Savings table (admin data)
*===============================================================================

*===============================================================================
* Section 1 - Prepare data for analysis
*===============================================================================

clear all
global path "~/file_server/project_savings/2_shared/impact/do/November2019"	// Path to processed data
global results "$path/results"		
global inputPath "$path/datasets"
cd "$results"									// Change to directory to store results
set more off
set trace off

*Loading processed data
	use $inputPath/impactDatabase_wide.dta, clear		

*Merge the asesor data here. 															
merge 1:1 idBaseSurvey using $inputPath/asesor2008.dta
drop _merge 																
							
*===============================================================================
* Section 2 - Effects on Total Financial Savings Table (Using Admin Savings Data)
*===============================================================================
* 2.1. Calculates table content ------------------------------------------------

	foreach y in 	PrallSavingsAd2009		IHSallSavingsAd2009		/// Panel A : Col1, Col2, Col6 are created here 
			PrnetSavingsAd2009		IHSnetSavingsAd2009		/// Panel B : Col2, Col6
			PrchangeTotalWealthAd2009	IHSchangeTotalWealthAd2009	/// Panel C : Col2, Col6
			{
	
	//locals for column numbers	
	*--------------------------			
	if "`y'" 		== "PrallSavingsAd2009" | "`y'" == "PrnetSavingsAd2009" | "`y'" == "PrchangeTotalWealthAd2009"  {
	local c 		= 2
	}
	else if "`y'"		== "IHSallSavingsAd2009" |"`y'" ==  "IHSnetSavingsAd2009" | "`y'" == "IHSchangeTotalWealthAd2009" {
	local c 		= 6
	}
	else if "`y'"		== "savingsCatAd2009"	{
	local c 		= 1
	}
	
	//locals for panels
	*-------------------
	if "`y'" 		== "savingsCatAd2009" | "`y'" == "PrallSavingsAd2009" | "`y'" == "IHSallSavingsAd2009"  {
	local p 		= 1 // or A in tables
	}
	else if "`y'"		== "PrnetSavingsAd2009" | "`y'" == "IHSnetSavingsAd2009"  {
	local p 		= 2 //or B in tables
	}
	else if "`y'"		== "PrchangeTotalWealthAd2009" | "`y'" == "IHSchangeTotalWealthAd2009" {
	local p			= 3 // or C in tables
	}
	
	local x = substr("`y'", 1, length("`y'") - 4) //Extracts variable name without year for regression
	
	gen nonmiss = 1 if `x'2009 !=. & `x'2008 !=. & inSample == 1 //Marking subjects nonmissing in both periods

		reghdfe `x'2009 c.accountAccess `x'2008 if nonmiss == 1, ///
			abs(asesor2008) vce(cluster groupId) 
	
	matrix list r(table) //Shows contents of stored matrix
	mat m = r(table)
	
	
	/*coef */		local P`p'C`c'R1: di %12.3fc m[1,1]
	/*s.e.*/		local P`p'C`c'R2: di %12.3fc m[2,1]
				local P`p'C`c'R2: di trim("`P`p'C`c'R2'")
	/*(s.e.) */		local P`p'C`c'R2="(`P`p'C`c'R2')"
	/*p-val*/		local P`p'C`c'pv: di %12.3fc m[4,1]
	
	di in red "`P`p'C`c'pv'"
	distinct idBaseSurvey if e(sample)
	
	gen `y'_s = e(sample) //marks observations used in the regression
	
***	/*individuals*/		local P`p'C`c'R4: di %12.0fc `r(ndistinct)'	
	/*obs*/			local P`p'C`c'R4: di %12.0fc `r(N)'
	
	sum `y' if accountAccess == 0 // control's mean outcome
	/*controlmean*/		local P`p'C`c'R3: di %12.3fc `r(mean)'
	/*Individual fe*/	local P`p'C`c'R6 = "No"
	/*StratumXPost fe*/	local P`p'C`c'R7 = "Yes"
	
	drop nonmiss
	di in red "Panel`p'Col`c'"
}

*----------------------------------------
*This is a different loop bc of decimal points in beta here being 0 not 3.
	foreach y in	w5_allSavingsAd2009		w1_allSavingsAd2009		allSavingsAd2009	/// Panel A : Col3, Col4, Col5 are created here 
			w5_netSavingsAd2009 		w1_netSavingsAd2009		netSavingsAd2009 	/// Panel B : Col3, Col4, Col5
			w5_changeTotalWealthAd2009 	w1_changeTotalWealthAd2009 	changeTotalWealthAd2009	/// Panel C : Col3, Col4, Col5
			{
					
	//locals for column numbers	
	*--------------------------			
	if "`y'" 		== "w5_allSavingsAd2009" | "`y'" == "w5_netSavingsAd2009" | "`y'" == "w5_changeTotalWealthAd2009" 		{
	local c 		= 3
	}
	else if "`y'"		== "w1_allSavingsAd2009" |"`y'" ==  "w1_netSavingsAd2009" | "`y'" == "w1_changeTotalWealthAd2009" 		{
	local c 		= 4
	}
	else if "`y'"		== "allSavingsAd2009" |"`y'" ==  "netSavingsAd2009" | "`y'" == "changeTotalWealthAd2009" 			{
	local c 		= 5
	}
	
	//locals for panels
	*-------------------
	if "`y'" 		== "w5_allSavingsAd2009" | "`y'" == "w1_allSavingsAd2009" | "`y'" == "allSavingsAd2009"				{
	local p 		= 1 // or A in tables
	}
	else if "`y'"		== "w5_netSavingsAd2009" | "`y'" == "w1_netSavingsAd2009" |  "`y'" == "netSavingsAd2009"			{
	local p 		= 2 //or B in tables
	}
	else if "`y'"		== "w5_changeTotalWealthAd2009" | "`y'" == "w1_changeTotalWealthAd2009" |"`y'" == "changeTotalWealthAd2009"	{
	local p			= 3 // or C in tables
	}
	
	local x = substr("`y'", 1, length("`y'") - 4) //Extracts variable name without year for regression
	
	gen nonmiss = 1 if `x'2009 !=. & `x'2008 !=. & inSample == 1 //Marking subjects nonmissing in both periods

		reghdfe `x'2009 c.accountAccess `x'2008 if nonmiss == 1, ///
			abs(asesor2008) vce(cluster groupId) 

	matrix list r(table) //Shows contents of stored matrix
	mat m=r(table)
	
	
	/*coef */		local P`p'C`c'R1: di %12.0fc m[1,1]
	/*s.e.*/		local P`p'C`c'R2: di %12.0fc m[2,1]
				local P`p'C`c'R2: di trim("`P`p'C`c'R2'")
	/*(s.e.) */		local P`p'C`c'R2="(`P`p'C`c'R2')"
	/*p-val*/		local P`p'C`c'pv: di %12.3fc m[4,1]
di in red "`P`p'C`c'pv'"

	distinct idBaseSurvey if e(sample)
	
	/*obs*/			local P`p'C`c'R4: di %12.0fc `r(N)'
	
	sum `y' if accountAccess == 0 // control's mean outcome
	/*controlmean*/		local P`p'C`c'R3: di %12.0fc `r(mean)'
	/*StratumXPost fe*/	local P`p'C`c'R7 = "Yes"
	
	drop nonmiss
	di in red "Panel`p'Col`c'"
}

// For savingsCatAd

/* Note: This is being done separately because there is a difference in the number
of observations across columns in the panel. Additional restrictions are added
to make sure that the same sample is used for each regression. */ 

	local y = "savingsCatAd"
					
	//locals for column numbers	
	*--------------------------			
	local c 		= 1
	
	//locals for panels
	*-------------------
	local p 		= 1 
				
	gen nonmiss = 1 if `y'2009 !=. & `y'2008 !=. & inSample == 1 //Marking subjects nonmissing in both periods		
	
	reghdfe `y'2009 c.accountAccess `y'2008 if nonmiss == 1 & PrallSavingsAd2009_s == 1, ///
			abs(asesor2008) vce(cluster groupId)

	matrix list r(table) //Shows contents of stored matrix
	mat m=r(table)
	
	
	/*coef */		local P`p'C`c'R1: di %12.3fc m[1,1]
	/*s.e.*/		local P`p'C`c'R2: di %12.3fc m[2,1]
				local P`p'C`c'R2: di trim("`P`p'C`c'R2'")
	/*(s.e.) */		local P`p'C`c'R2="(`P`p'C`c'R2')"
	/*p-val*/		local P`p'C`c'pv: di %12.3fc m[4,1]

	distinct idBaseSurvey if e(sample)
	
	/*individuals*/		local P`p'C`c'R4: di %12.0fc `r(ndistinct)'
	/*obs*/			local P`p'C`c'R5: di %12.0fc `r(N)'
	
	sum `y'2009 if accountAccess == 0 // control's mean outcome
	/*controlmean*/		local P`p'C`c'R3: di %12.3fc `r(mean)'
	/*Individual fe*/	local P`p'C`c'R6 = "Yes"
	/*StratumXPost fe*/	local P`p'C`c'R7 = "Yes"
	
	drop nonmiss 
	di in red "Panel`p'Col`c'"

*** Put stars in regression coefficients
	forvalue c=1/1	{
	forvalue p=1/1	{
	*stars
	if `P`p'C`c'pv'<=0.10 & `P`p'C`c'pv'>0.05 {
	local P`p'C`c'R1="$`P`p'C`c'R1'^{*}$"
	}
	else if  `P`p'C`c'pv'<=0.05 &  `P`p'C`c'pv'>0.01 {
	local P`p'C`c'R1="$`P`p'C`c'R1'^{**}$"
	}
	else if  `P`p'C`c'pv'<=0.01 { 
	local P`p'C`c'R1="$`P`p'C`c'R1'^{***}$"
	}
	}
	}
	
*** Put stars in regression coefficients
	forvalue c=2/6	{
	forvalue p=1/3	{
	*stars
	if `P`p'C`c'pv'<=0.10 & `P`p'C`c'pv'>0.05 {
	local P`p'C`c'R1="$`P`p'C`c'R1'^{*}$"
	}
	else if  `P`p'C`c'pv'<=0.05 &  `P`p'C`c'pv'>0.01 {
	local P`p'C`c'R1="$`P`p'C`c'R1'^{**}$"
	}
	else if  `P`p'C`c'pv'<=0.01 { 
	local P`p'C`c'R1="$`P`p'C`c'R1'^{***}$"
	}
	}
	}	
	
//Add empty locals for Col2 Panel B and Col3 Panel C (as Categories for savings is missing for netsaving and netwealth)
	local c = 1
	forval p = 2/3	{
	forval r = 1/7	{
	local P`p'C`c'R`r' = ""
	}
	}
	
// Row titles

	local lbl_1 "Account"	
	local lbl_2 ""
	local lbl_3 "Control mean"
***	local lbl_4 "Individuals"
	local lbl_4 "Observations"

	local lbl_6 "Individual FE"
	local lbl_7 "Stratum FE"

//Table code:
	texdoc init $results/tab_savings_admin_ancova.tex, replace force		// Note: Need to include tablurax environment in latex 	
	tex \\ \\ [-1.5ex]
	tex \hline\hline \\ [-1.5ex]
	tex 	& (1) 				& (2)				  & (3) 			& (4) 				 & (5)	 		    & (6)		\\
	tex {}	& Categories 	& Probability of any saving		& Amounts (winsorized at 5\%) 	& Amounts (winsorized at 1\%) 	 & Amounts (non-winsorized) & IHS of amounts	\\ 
	tex \hline
	tex \\
	tex \multicolumn{2}{l}{\textbf{Panel A:} Total Financial Savings} & & & & & \\
	forvalue t=1/4 {
	tex `lbl_`t'' & `P1C1R`t'' & `P1C2R`t''	 & `P1C3R`t'' 	& `P1C4R`t'' 	& `P1C5R`t'' 	& `P1C6R`t'' 	 \\
	}
	tex \\
	tex \multicolumn{2}{l}{\textbf{Panel B:} Net Total Financial Savings (incl. Borrowing)}  & & & & & \\
	forvalue t=1/4 {
	tex `lbl_`t'' & `P2C1R`t'' & `P2C2R`t''	 & `P2C3R`t'' 	& `P2C4R`t'' 	& `P2C5R`t'' 	& `P2C6R`t'' 	 \\
	}
	tex \\
	tex \multicolumn{3}{l}{\textbf{Panel C:} Total Financial Assets (incl. Borrowing and Lending)} & & & & \\
	forvalue t=1/4 {
	tex `lbl_`t'' & `P3C1R`t'' & `P3C2R`t''	 & `P3C3R`t'' 	& `P3C4R`t'' 	& `P3C5R`t'' 	& `P3C6R`t'' 	 \\
	}
	tex \\ 
	tex  \hline 
	forvalue t=7/7 {
	tex `lbl_`t'' & `P2C1R`t'' & `P2C2R`t'' & `P2C3R`t'' & `P2C4R`t'' & `P2C5R`t'' & `P2C6R`t'' \\
	}
	tex  \hline\hline \\ [-1.5ex]
	texdoc close
	
